package com.x.ant.dao;

import com.x.ant.model.DeptUser;
import com.x.ant.model.Sender;
import com.x.ant.model.Staff;
import com.x.ant.model.User;
import com.x.framework.dao.BaseDao;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

@Repository
public class UserDao extends BaseDao {

    private final static String selectUserListSql = "SELECT T1.* FROM ANT_USER T1, ANT_DEPT_USER T2 WHERE T1.USER_ID = T2.USER_ID ";
    public List<User> selectUserList(DeptUser deptUser) throws Exception{
        StringBuilder sb = new StringBuilder(selectUserListSql);
        List params = new ArrayList();
        if(this.isString(deptUser.getUser().getUserMobile())){
            sb.append("AND T1.USER_MOBILE = ? ");
            params.add(deptUser.getUser().getUserMobile());
        }
        if(this.isString(deptUser.getUser().getUserName())){
            sb.append("AND T1.USER_NAME = ? ");
            params.add(deptUser.getUser().getUserName());
        }
        if(deptUser.getUser().getUserType() != null){
            sb.append("AND T1.USER_TYPE = ? ");
            params.add(deptUser.getUser().getUserType());
        }
        if(this.isString(deptUser.getDeptId())){
            sb.append("AND T2.DEPT_ID = ? ");
            params.add(deptUser.getDeptId());
        }
        if(this.isString(deptUser.getDeptParentId())){
            sb.append("AND T2.DEPT_PARENT_ID = ? ");
            params.add(deptUser.getDeptParentId());
        }
        sb.append("ORDER BY T1.USER_TYPE, T1.USER_NAME");
        return this.queryForList(sb.toString(), User.class, params, deptUser);
    }

    private final static String selectUserNumSql = "SELECT COUNT(1) FROM ANT_USER T1, ANT_DEPT_USER T2 WHERE T1.USER_ID = T2.USER_ID ";
    public int selectUserNum(DeptUser deptUser) throws Exception{
        StringBuilder sb = new StringBuilder(selectUserNumSql);
        List params = new ArrayList();
        if(this.isString(deptUser.getUser().getUserMobile())){
            sb.append("AND T1.USER_MOBILE = ? ");
            params.add(deptUser.getUser().getUserMobile());
        }
        if(this.isString(deptUser.getUser().getUserName())){
            sb.append("AND T1.USER_NAME = ? ");
            params.add(deptUser.getUser().getUserName());
        }
        if(deptUser.getUser().getUserType() != null){
            sb.append("AND T1.USER_TYPE = ? ");
            params.add(deptUser.getUser().getUserType());
        }
        if(this.isString(deptUser.getDeptId())){
            sb.append("AND T2.DEPT_ID = ? ");
            params.add(deptUser.getDeptId());
        }
        if(this.isString(deptUser.getDeptParentId())){
            sb.append("AND T2.DEPT_PARENT_ID = ? ");
            params.add(deptUser.getDeptParentId());
        }
        return this.queryForInt(sb.toString(), params);
    }

    private final static String selectStaffListSql = "SELECT T1.*, T2.*, T3.* " +
            "FROM ANT_DEPT T1, ANT_USER T2, ANT_STAFF T3 WHERE T1.DEPT_ID = T3.DEPT_ID AND T2.USER_ID = T3.USER_ID ";
    public List<Staff> selectStaffList(Staff staff) throws Exception{
        StringBuilder sb = new StringBuilder(selectStaffListSql);
        List params = new ArrayList();
        if(this.isString(staff.getStaffId())){
            sb.append("AND T3.STAFF_ID = ? ");
            params.add(staff.getStaffId());
        }
        if(this.isString(staff.getDeptId())){
            sb.append("AND T3.DEPT_ID = ? ");
            params.add(staff.getDeptId());
        }
        if(this.isString(staff.getUserId())){
            sb.append("AND T3.USER_ID = ? ");
            params.add(staff.getUserId());
        }
        if(staff.getStaffType() != null){
            sb.append("AND T3.STAFF_TYPE = ? ");
            params.add(staff.getStaffType());
        }
        if(staff.getStaffState() != null){
            sb.append("AND T3.STAFF_STATE = ? ");
            params.add(staff.getStaffState());
        }
        if(staff.getUser() != null && this.isString(staff.getUser().getUserMobile())){
            sb.append("AND T2.USER_MOBILE = ? ");
            params.add(staff.getUser().getUserMobile());
        }
        if(staff.getUser() != null && this.isString(staff.getUser().getUserName())){
            sb.append("AND T2.USER_NAME = ? ");
            params.add(staff.getUser().getUserName());
        }
        return this.queryForList(sb.toString(), Staff.class, params, staff);
    }

    private final static String selectSenderListSql = "SELECT T2.USER_ID, " +
            "T2.USER_MOBILE, " +
            "T2.USER_NAME, " +
            "T2.USER_TYPE, " +
            "T2.USER_STATE, " +
            "T3.SENDER_ID, " +
            "T3.DEPT_ID, " +
            "T3.SENDER_LAST_GOOD, " +
            "T3.SENDER_LAST_DEST, " +
            "T4.PROVINCE_ID, " +
            "T4.PROVINCE_NAME, " +
            "T5.CITY_ID, " +
            "T5.CITY_NAME, " +
            "T6.DISTRICT_ID, " +
            "T6.DISTRICT_NAME " +
            "FROM ANT_USER T2, ANT_SENDER T3, BASE_PROVINCE T4, BASE_CITY T5, BASE_DISTRICT T6 " +
            "WHERE T2.USER_ID = T3.USER_ID " +
            "AND T3.PROVINCE_ID = T4.PROVINCE_ID " +
            "AND T3.CITY_ID = T5.CITY_ID " +
            "AND T3.DISTRICT_ID = T6.DISTRICT_ID ";
    public List<Sender> selectSenderList(Sender sender) throws Exception{
        StringBuilder sb = new StringBuilder(selectSenderListSql);
        List params = new ArrayList();
        if(this.isString(sender.getProvinceId())){
            sb.append("AND T3.PROVINCE_ID = ? ");
            params.add(sender.getProvinceId());
        }
        if(this.isString(sender.getCityId())){
            sb.append("AND T3.CITY_ID = ? ");
            params.add(sender.getCityId());
        }
        if(this.isString(sender.getDistrictId())){
            sb.append("AND T3.DISTRICT_ID = ? ");
            params.add(sender.getDistrictId());
        }
        if(this.isString(sender.getSenderId())){
            sb.append("AND T3.SENDER_ID = ? ");
            params.add(sender.getSenderId());
        }
        if(this.isString(sender.getDeptId())){
            sb.append("AND T3.DEPT_ID = ? ");
            params.add(sender.getDeptId());
        }
        if(this.isString(sender.getDeptParentId())){
            sb.append("AND T3.DEPT_PARENT_ID = ? ");
            params.add(sender.getDeptParentId());
        }
        if(this.isString(sender.getUserId())){
            sb.append("AND T3.USER_ID = ? ");
            params.add(sender.getUserId());
        }
        if(sender.getUser() != null && this.isString(sender.getUser().getUserName())){
            sb.append("AND T2.USER_NAME = ? ");
            params.add(sender.getUser().getUserName());
        }
        if(sender.getUser() != null && this.isString(sender.getUser().getUserMobile())){
            sb.append("AND T2.USER_MOBILE = ? ");
            params.add(sender.getUser().getUserMobile());
        }
        sb.append("ORDER BY T2.USER_CREATE_TIME DESC");
        return this.queryForList(sb.toString(), Sender.class, params, sender);
    }

    private final static String selectSenderNumSql = "SELECT COUNT(1) " +
            "FROM ANT_USER T2, ANT_SENDER T3 " +
            "WHERE T2.USER_ID = T3.USER_ID ";
    public int selectSenderNum(Sender sender) throws Exception{
        StringBuilder sb = new StringBuilder(selectSenderNumSql);
        List params = new ArrayList();
        if(this.isString(sender.getProvinceId())){
            sb.append("AND T3.PROVINCE_ID = ? ");
            params.add(sender.getProvinceId());
        }
        if(this.isString(sender.getCityId())){
            sb.append("AND T3.CITY_ID = ? ");
            params.add(sender.getCityId());
        }
        if(this.isString(sender.getDistrictId())){
            sb.append("AND T3.DISTRICT_ID = ? ");
            params.add(sender.getDistrictId());
        }
        if(this.isString(sender.getSenderId())){
            sb.append("AND T3.SENDER_ID = ? ");
            params.add(sender.getSenderId());
        }
        if(this.isString(sender.getDeptId())){
            sb.append("AND T3.DEPT_ID = ? ");
            params.add(sender.getDeptId());
        }
        if(this.isString(sender.getDeptParentId())){
            sb.append("AND T3.DEPT_PARENT_ID = ? ");
            params.add(sender.getDeptParentId());
        }
        if(this.isString(sender.getUserId())){
            sb.append("AND T3.USER_ID = ? ");
            params.add(sender.getUserId());
        }
        if(sender.getUser() != null && this.isString(sender.getUser().getUserName())){
            sb.append("AND T2.USER_NAME = ? ");
            params.add(sender.getUser().getUserName());
        }
        if(sender.getUser() != null && this.isString(sender.getUser().getUserMobile())){
            sb.append("AND T2.USER_MOBILE = ? ");
            params.add(sender.getUser().getUserMobile());
        }
        if(sender.getUser() != null && sender.getUser().getUserCreateTime() != null){
            sb.append("AND DATE(T2.USER_CREATE_TIME) = ? ");
            params.add(sender.getUser().getUserCreateTime());
        }
        return this.queryForInt(sb.toString(), params);
    }

}
